#!/usr/bin/env python3
"""
SQLite 后端
POST /save  {"text":"买牛奶"}
GET  /list   → [{"id":1,"text":"...","time":"2025-09-14 20:00:00"},...]
"""
import sqlite3, json, time, sys
from http.server import HTTPServer, BaseHTTPRequestHandler
DB_FILE = '/home/ubuntu/myenv2/notes.db'

def init_db():
    conn = sqlite3.connect(DB_FILE)
    conn.execute('''
        CREATE TABLE IF NOT EXISTS notes(
          id   INTEGER PRIMARY KEY AUTOINCREMENT,
          text TEXT NOT NULL,
          time TEXT NOT NULL
        );
    ''')
    conn.commit(); conn.close()

class Handler(BaseHTTPRequestHandler):
    def do_POST(self):
        if self.path == '/save':
            length = int(self.headers['Content-Length'])
            body = self.rfile.read(length).decode()
            try:
                data = json.loads(body)
                text = data['text']
            except Exception:
                self.send_error(400, 'bad json')
                return
            conn = sqlite3.connect(DB_FILE)
            conn.execute('INSERT INTO notes(text,time) VALUES(?,?)',
                         (text, time.strftime('%Y-%m-%d %H:%M:%S')))
            conn.commit(); conn.close()
            self.send_response(200)
            self.end_headers()
            self.wfile.write(b'{"ok":true}')
        else:
            self.send_error(404)

    def do_GET(self):
        if self.path == '/list':
            conn = sqlite3.connect(DB_FILE)
            rows = conn.execute('SELECT id,text,time FROM notes ORDER BY id DESC').fetchall()
            conn.close()
            notes = [{'id':r[0],'text':r[1],'time':r[2]} for r in rows]
            self.send_response(200)
            self.send_header('Content-Type', 'application/json; charset=utf-8')
            # 允许前端跨域
            self.send_header('Access-Control-Allow-Origin', '*')
            self.end_headers()
            self.wfile.write(json.dumps(notes, ensure_ascii=False).encode())
        else:
            self.send_error(404)

if __name__ == '__main__':
    init_db()
    base_port = int(sys.argv[1]) if len(sys.argv) > 1 else 8082
    for delta in range(20):          # 最多试 20 个端口
        port = base_port + delta
        try:
            print(f"SQLite API listening on {port}")
            HTTPServer(('0.0.0.0', port), Handler).serve_forever()
        except OSError as e:
            if e.errno == 98:        # Address already in use
                print(f"Port {port} busy, trying {port + 1}")
                continue
            raise
        break
    else:
        print("All 20 ports busy, give up.")